package cn.edu.swu.lzr.Common;



import cn.edu.swu.lzr.Book.Book;
import org.apache.commons.dbcp2.BasicDataSource;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBTool {
    private static DBTool instance = new DBTool();
    private static BasicDataSource dataSource;

    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            dataSource = new BasicDataSource();
            dataSource.setUrl("jdbc:mysql://localhost:3306/bookstore");
            dataSource.setUsername("root");
            dataSource.setPassword("1911lzrnb");
            dataSource.setMinIdle(5);
            dataSource.setMaxIdle(7);
            dataSource.setMaxTotal(20);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    public DBTool(){
    }

    public static DBTool getInstance() {
        return instance;
    }

    public Connection getDBConnection() throws SQLException {
        return dataSource.getConnection();
    }

    public boolean addBook(Book book) throws SQLException {
        String insertTemplate = "insert into book(name, author, price) values ('%s', '%s', %s)";
        String sql = String.format(insertTemplate, book.getName(), book.getAuthor(), book.getPrice());
        return this.execute(sql);
    }

    public boolean deleteBookById(Integer id) throws SQLException {
        String template = "delete from book where id=%s";
        String sql = String.format(template, id);
        return this.execute(sql);
    }

    protected boolean execute(String cudSQL) throws SQLException {
        try(Connection connection = dataSource.getConnection()) {
            try(Statement statement = connection.createStatement()){
                return statement.execute(cudSQL);
            }
        }
    }

    protected void query(String sql, ResultSetVisitor visitor) throws SQLException {
        try(Connection connection = dataSource.getConnection()) {
            try(Statement statement = connection.createStatement()) {
                try(ResultSet resultSet = statement.executeQuery(sql)) {
                    while (resultSet.next()) {
                        visitor.visit(resultSet);
                    }
                }
            }
        }
    }

}